#!/usr/bin/env python
# -*- coding:utf-8 -*-
__author__ = 'wshu'
"""
    ***********************************
    *  @filename : db.py
    *  @Author : wshu
    *  @CodeDate : 2019/12/14 22:10
    *  @Software : PyCharm
    ***********************************
"""
import pymysql
import datetime
import traceback
from scrapy.utils.project import get_project_settings
assert get_project_settings().get('MYSQL_CONF')
dbconf = get_project_settings().get('MYSQL_CONF')

class Db(object):
    """
    Mysql惰性封装,只有在实际用到才会去链接
    """
    def __init__(self):
        self.host = dbconf.get('MYSQL_HOST')
        self.port = dbconf.get('MYSQL_PORT')
        self.db = dbconf.get('MYSQL_DB')
        self.user = dbconf.get('MYSQL_USER')
        self.passwd = dbconf.get('MYSQL_PWD')
        self.charset = dbconf.get('MYSQL_CHARSET')
        self._conn = self._connect()
        if (self._conn):
            self._cursor = self._conn.cursor()

    # 连接
    def _connect(self):
        conn = False
        try:
            conn = pymysql.connect(host=self.host,
                                   port=self.port,
                                   db=self.db,
                                   user=self.user,
                                   passwd=self.passwd,
                                   cursorclass=pymysql.cursors.DictCursor,
                                   charset=self.charset,
                                   use_unicode=True)
        except pymysql.Error as err:
            print("[ERROR] ", str(err))
            traceback.print_exc()
        return conn

    def excute(self, sql):
        try:
            res = self._cursor.execute(sql)
            print('执行sql受影响的行数：', res)
        except pymysql.Error as err:
            res = False
            print(err)
    # 查询全部数据
    def query_all(self, sql):
        res = ''
        if (self._conn):
            try:
                self._cursor.execute(sql)
                res = self._cursor.fetchall()
            except pymysql.Error as err:
                res = False
                print(err)
            return res
    # 查询一条数据
    @property
    def query_one(self):
        sql = "select site_url,site_id,task_id from site_url where site_state=%s and task_id=%s limit 1 for update"
        self._cursor.execute(sql, (1, 12))
        result = self._cursor.fetchone()
        if result:
            url = result.get('site_url')
            sql_up = "update site_url set site_state=%s,site_start_time=%s,site_end_time=%s where site_url=%s"
            self._cursor.execute(sql_up, (2, datetime.datetime.now(), datetime.datetime.now(), url))
            self.commitSQL()
            # self.close()
            return result
        return result

    # 更新数据
    def update(self, site_url):
        if (self._conn):
            try:
                sql = "update site_url set site_state=%s,site_start_time=%s,site_end_time=%s where site_url=%s limit 1"
                self._cursor.execute(sql, (2, datetime.datetime.now(), datetime.datetime.now(), site_url))
            except Exception as err:
                print('update error: ', err)
                self.close()
    # 插入数据
    def insert(self, sql):
        flag = False
        if (self._conn):
            try:
                self._cursor.execute(sql)
                self._conn.commit()
                flag = True
            except Exception as err:
                print('update error: ', err)
                self.close()
        return flag

    def commitSQL(self):
        # 提交事务
        self._conn.commit()

    # 释放数据库资源
    def close(self):
        if self._conn and self._cursor:
            self._cursor.close()
            self._conn.close()
        return True

if __name__ == '__main__':
    import datetime
    db = Db()
    data = db.query_one
    print(">>>:", data)
    # site_url = "http://zuqiuxitongpingtai.shzyzz.com"
    # ttime = datetime.datetime.now()
    # sql_update = "update site_url set site_state=2,site_start_time='%s',site_end_time='%s' where site_url='%s'"\
    #              %(ttime, ttime, site_url)
    # db.update(sql_update)
